﻿
/***************************************************************************

Copyright (c) BizRulX. All rights reserved.

***************************************************************************/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml;
using System.Data.OleDb;
using System.Configuration;
//using ExcelReading;
//using EnvDTE90;
using EnvDTE;
using EnvDTE80;
using System.IO;
using System.Xml.Schema;
using Microsoft.VisualStudio.Shell.Interop;

namespace RuleXSoft.BizRuleCreator
{
    [Serializable]
    public class BizRuleMapper
    {
        private DTE2 _applicationObject;
        private AddIn _addInInstance;
        private IVsGeneratorProgress codeGeneratorProgress;

        /// <summary>
        /// Interface to the VS shell object we use to tell our progress while we are generating
        /// </summary>
        internal IVsGeneratorProgress CodeGeneratorProgress
        {
            get
            {
                return codeGeneratorProgress;
            }
        }

        internal static bool validXML;

        public DataTable GetAttributesForMapping(List<string> objExcelFieldList, List<string> objXMLFieldList)
        {
            DataSet dsAttMap = new DataSet();
            DataTable dtAttMap = new DataTable();

            //preparing table column
            //DataColumn colExcelAtt = new DataColumn("ExcelAttCol");
            dtAttMap.Columns.Add(new DataColumn("ExcelAttCol"));
            dtAttMap.Columns.Add(new DataColumn("XMLAttCol"));
            dtAttMap.Columns.Add(new DataColumn("Direction"));

            
            return dtAttMap;
        }

        public DataTable PrepareMappingElements(string excelFilePath, string workSheetName)
        {
            try
            {
                DataSet dsAttMap = new DataSet();
                DataTable dtAttMap = new DataTable();

                List<string> objExcelFieldList = new List<string>();
                List<string> objXMLFieldList = new List<string>();

                dsAttMap = ReadExcelFile(excelFilePath, workSheetName);
                foreach (DataColumn dc in dsAttMap.Tables[0].Columns)
                {
                    objExcelFieldList.Add(dc.Caption.ToString());
                }

                //Removing space charecter if any
                RemoveSpace(dsAttMap);
                //Generate XML file for input excel file
                GenerateXML(dsAttMap, null);

                //preparing table column
                //DataColumn colExcelAtt = new DataColumn("ExcelAttCol");
                dtAttMap.Columns.Add(new DataColumn("ExcelAttCol"));
                dtAttMap.Columns.Add(new DataColumn("XMLAttCol"));
                dtAttMap.Columns.Add(new DataColumn("Direction"));


                return dtAttMap;
            }
            catch (Exception ex)
            {
                
                throw ex;
            }
        }

        public DataSet ReadExcelFile(string excelFilePath, string workSheetName)
        {
            
            try
            {
                
                string _connectionString = string.Format(
                    "Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", excelFilePath);
                //"Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", @"K:\Visual Studio 2008\Projects\CSharpGeneral\ExcelReading V1.3\Excel\BizRuleSet.xls");

                OleDbDataAdapter _adaptor = new OleDbDataAdapter("select * from [" + workSheetName + "$]", _connectionString);
                DataSet ds = new DataSet();
                

                _adaptor.Fill(ds);
                //RemoveSpace(ds);
                //WriteOutput(ds);

                //GenerateXML(ds);

                return ds;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            
        }

        private static void RemoveSpace(DataSet ds)
        {
            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
            {
                ds.Tables[0].Columns[i].Caption.ToString().Replace(" ", "").Trim();
            }
        }

        public void GenerateXML(DataSet ds, RulesetObject objRuleset)
        {
            try
            {
                XmlDocument xmlDoc = new XmlDocument();

                // Write down the XML declaration
                XmlDeclaration xmlDeclaration = xmlDoc.CreateXmlDeclaration("1.0", "utf-8", null);

                // Create the root element
                XmlElement rootNode = xmlDoc.CreateElement("Types");
                //rootNode.SetAttribute("xmlns", "http://microsoft.com/XMLClassGeneratorSchema.xsd");
                xmlDoc.InsertBefore(xmlDeclaration, xmlDoc.DocumentElement);
                xmlDoc.AppendChild(rootNode);

                XmlElement rootRuleSetName = xmlDoc.CreateElement("RuleSetName");
                rootRuleSetName.SetAttribute("text", objRuleset.RulesetName);
                rootNode.AppendChild(rootRuleSetName);

                XmlElement rootRuleSetDesc = xmlDoc.CreateElement("RuleSetDescription");
                rootRuleSetDesc.SetAttribute("text", objRuleset.RulesetDescription);
                rootNode.AppendChild(rootRuleSetDesc);

                XmlElement rootRuleSetClassNode = xmlDoc.CreateElement("RuleSetClass");
                rootRuleSetClassNode.SetAttribute("name", objRuleset.RulesetAttributeClassName);
                rootNode.AppendChild(rootRuleSetClassNode);

                //Field declaration part
                XmlElement declarationNode = xmlDoc.CreateElement("Declaration");
                rootRuleSetClassNode.AppendChild(declarationNode);

                XmlElement fieldsNode = xmlDoc.CreateElement("Fields");
                declarationNode.AppendChild(fieldsNode);


                for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                {
                    //XmlElement parentDeclarationFieldNode = xmlDoc.CreateElement("Field");
                    //fieldsNode.AppendChild(parentDeclarationFieldNode);
                    //Excluding the column user has selected
                    if (!ds.Tables[0].Columns[j].ColumnName.Trim().ToUpper().Substring(0, 8).Equals("EXCLUDE_"))
                    {
                        IList<string> listXmlElemets = ds.Tables[0].Columns[j].Caption.ToString().Split('|');

                        //storing XML element key name / direction attribute and datatype
                        string strMainKeyName = listXmlElemets[0].ToString();
                        string strKeyDataType = listXmlElemets[2].ToString();
                        string strKeyDirection = listXmlElemets[1].ToString();
                        string strKeyIsDerived = listXmlElemets[3].ToString();

                        string strKeyIsAllowNull = listXmlElemets[4].ToString();
                            

                        //Create the required nodes and attribute
                        XmlElement mainFieldNode = xmlDoc.CreateElement("Field");
                        //XmlElement mainNode = xmlDoc.CreateElement(ds.Tables[0].Columns[j].Caption.ToString().Replace(" ", "").Trim());
                        mainFieldNode.SetAttribute("name", strMainKeyName.Replace(" ", "").Trim());
                        mainFieldNode.SetAttribute("type", strKeyDataType.Replace(" ", "").Trim());
                        mainFieldNode.SetAttribute("direction", strKeyDirection.Replace(" ", "").Trim());
                        mainFieldNode.SetAttribute("derived", strKeyIsDerived.Replace(" ", "").Trim());

                        

                        if (strKeyIsAllowNull.Trim().ToUpper().Equals("NULLNOTALLOWED"))
                        {
                            mainFieldNode.SetAttribute("allownull", "false");
                        }
                        else
                        {
                            mainFieldNode.SetAttribute("allownull", "true");
                        }
                        //for mapping in generated class property names
                            
                        // append the nodes to the parentNode without the value
                        fieldsNode.AppendChild(mainFieldNode);
                    }
                }
 

                ////////////////////////////////////////////////////
                XmlElement rootRuleSet = xmlDoc.CreateElement("RuleSet");
                rootRuleSetClassNode.AppendChild(rootRuleSet);

                for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {
                    // Create a new <Rule> element and add it to the root node
                    XmlElement parentNode = xmlDoc.CreateElement("Rule");

                    // Set attribute name and value!
                    parentNode.SetAttribute("ID", (i + 1).ToString());

                    xmlDoc.DocumentElement.PrependChild(parentNode);

                    rootRuleSet.AppendChild(parentNode);
                    for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                    {
                        //Excluding the column user has selected
                        if (!ds.Tables[0].Columns[j].ColumnName.Trim().ToUpper().Substring(0,8).Equals("EXCLUDE_"))
                        {
                            IList<string> listXmlElemets = ds.Tables[0].Columns[j].Caption.ToString().Split('|');

                            //storing XML element key name / direction attribute and datatype
                            string strMainKeyName = listXmlElemets[0].ToString();
                            string strKeyDirection = listXmlElemets[1].ToString();
                            string strKeyDataType = listXmlElemets[2].ToString();
                            string strIsDerived = listXmlElemets[3].ToString();

                            string strIsAllowedNull = listXmlElemets[4].ToString();

                            //Create the required nodes and attribute
                            XmlElement mainNode = xmlDoc.CreateElement("data");
                            //XmlElement mainNode = xmlDoc.CreateElement(ds.Tables[0].Columns[j].Caption.ToString().Replace(" ", "").Trim());
                            mainNode.SetAttribute("key", strMainKeyName.Replace(" ", "").Trim());
                            //for mapping in generated class property names
                            //mainNode.SetAttribute("objectmappingname", ds.Tables[0].Columns[j].Caption.ToString().Replace(" ", "").Trim());

                            mainNode.SetAttribute("operator", "EQUALS");
                            //if the value is provided from derived column
                            mainNode.SetAttribute("value", ds.Tables[0].Rows[i][j].ToString());
                            mainNode.SetAttribute("direction", strKeyDirection);
                            if (strIsDerived.Trim().ToUpper().Equals("NOTDERIVED"))
                            {
                                mainNode.SetAttribute("derived", "false");
                            }
                            else
                            {
                                mainNode.SetAttribute("derived", "true");
                            }

                            if (strIsAllowedNull.Trim().ToUpper().Equals("NULLNOTALLOWED"))
                            {
                                mainNode.SetAttribute("allownull", "false");
                            }
                            else
                            {
                                mainNode.SetAttribute("allownull", "true");
                            }
                            
                            if (ds.Tables[0].Rows[i][j].ToString().ToUpper().Equals("TRUE")
                                || ds.Tables[0].Rows[i][j].ToString().ToUpper().Equals("FALSE")
                                || ds.Tables[0].Rows[i][j].ToString().ToUpper().Equals("YES")
                                || ds.Tables[0].Rows[i][j].ToString().ToUpper().Equals("NO"))
                            {
                                mainNode.SetAttribute("datatype", "boolean");
                            }
                            else
                            {
                                mainNode.SetAttribute("datatype", strKeyDataType);
                            }
                            // append the nodes to the parentNode without the value
                            parentNode.AppendChild(mainNode);

                            // Save to the XML file

                            if (!string.IsNullOrEmpty(objRuleset.RulesetFileOutputDirectory) && !string.IsNullOrEmpty(objRuleset.RulesetFileName))
                            {
                                xmlDoc.Save(objRuleset.RulesetFileOutputDirectory + @"\" + objRuleset.RulesetFileName + @".rulx");
                            } 
                        }
                    }

                    
                }
            }
            catch (Exception ex)
            {
                
                throw ex;
            }
        }


        public void UpdateRulXXML(DataSet ds, RulesetObject objRuleSet)
        {
            try
            {
                XmlDocument xmlDoc = new XmlDocument();

                // Write down the XML declaration
                XmlDeclaration xmlDeclaration = xmlDoc.CreateXmlDeclaration("1.0", "utf-8", null);

                // Create the root element
                XmlElement rootNode = xmlDoc.CreateElement("Types");
                //rootNode.SetAttribute("xmlns", "http://microsoft.com/XMLClassGeneratorSchema.xsd");
                xmlDoc.InsertBefore(xmlDeclaration, xmlDoc.DocumentElement);
                xmlDoc.AppendChild(rootNode);

                XmlElement rootRuleSetName = xmlDoc.CreateElement("RuleSetName");
                rootRuleSetName.SetAttribute("text", objRuleSet.RulesetName);
                rootNode.AppendChild(rootRuleSetName);

                XmlElement rootRuleSetDesc = xmlDoc.CreateElement("RuleSetDescription");
                rootRuleSetDesc.SetAttribute("text", objRuleSet.RulesetDescription);
                rootNode.AppendChild(rootRuleSetDesc);

                XmlElement rootRuleSetClassNode = xmlDoc.CreateElement("RuleSetClass");
                rootRuleSetClassNode.SetAttribute("name", objRuleSet.RulesetAttributeClassName);
                rootNode.AppendChild(rootRuleSetClassNode);

                //Field declaration part
                XmlElement declarationNode = xmlDoc.CreateElement("Declaration");
                rootRuleSetClassNode.AppendChild(declarationNode);

                XmlElement fieldsNode = xmlDoc.CreateElement("Fields");
                declarationNode.AppendChild(fieldsNode);


                for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                {
                    //XmlElement parentDeclarationFieldNode = xmlDoc.CreateElement("Field");
                    //fieldsNode.AppendChild(parentDeclarationFieldNode);
                    //Excluding the column user has selected
                    if (!ds.Tables[0].Columns[j].ColumnName.Trim().ToUpper().StartsWith("EXCLUDE_"))
                    {
                        IList<string> listXmlElemets = ds.Tables[0].Columns[j].Caption.ToString().Split('|');

                        //storing XML element key name / direction attribute and datatype
                        string strMainKeyName = listXmlElemets[0].ToString();
                        string strKeyDataType = listXmlElemets[2].ToString();
                        string strKeyDirection = listXmlElemets[1].ToString();
                        string strKeyIsDerived = listXmlElemets[3].ToString();

                        string strKeyIsAllowedNull = listXmlElemets[4].ToString();


                        //Create the required nodes and attribute
                        XmlElement mainFieldNode = xmlDoc.CreateElement("Field");
                        //XmlElement mainNode = xmlDoc.CreateElement(ds.Tables[0].Columns[j].Caption.ToString().Replace(" ", "").Trim());
                        mainFieldNode.SetAttribute("name", strMainKeyName.Replace(" ", "").Trim());
                        mainFieldNode.SetAttribute("type", strKeyDataType.Replace(" ", "").Trim());
                        mainFieldNode.SetAttribute("direction", strKeyDirection.Replace(" ", "").Trim());
                        mainFieldNode.SetAttribute("derived", strKeyIsDerived.Replace(" ", "").Trim());

                        

                        if (strKeyIsAllowedNull.Trim().ToUpper().Equals("NULLNOTALLOWED"))
                        {
                            mainFieldNode.SetAttribute("allownull", "false");
                        }
                        else
                        {
                            mainFieldNode.SetAttribute("allownull", "true");
                        }
                        //for mapping in generated class property names

                        // append the nodes to the parentNode without the value
                        fieldsNode.AppendChild(mainFieldNode);
                    }
                }


                ////////////////////////////////////////////////////
                XmlElement rootRuleSet = xmlDoc.CreateElement("RuleSet");
                rootRuleSetClassNode.AppendChild(rootRuleSet);

                for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {
                    // Create a new <Rule> element and add it to the root node
                    XmlElement parentNode = xmlDoc.CreateElement("Rule");

                    // Set attribute name and value!
                    parentNode.SetAttribute("ID", (i + 1).ToString());

                    xmlDoc.DocumentElement.PrependChild(parentNode);

                    rootRuleSet.AppendChild(parentNode);
                    for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                    {
                        //Excluding the column user has selected
                        if (!ds.Tables[0].Columns[j].ColumnName.Trim().ToUpper().StartsWith("EXCLUDE_"))
                        {
                            IList<string> listXmlElemets = ds.Tables[0].Columns[j].Caption.ToString().Split('|');

                            //storing XML element key name / direction attribute and datatype
                            string strMainKeyName = listXmlElemets[0].ToString();
                            string strKeyDirection = listXmlElemets[1].ToString();
                            string strKeyDataType = listXmlElemets[2].ToString();
                            string strIsDerived = listXmlElemets[3].ToString();

                            string strIsAllowedNull = listXmlElemets[4].ToString();

                            //Create the required nodes and attribute
                            XmlElement mainNode = xmlDoc.CreateElement("data");
                            //XmlElement mainNode = xmlDoc.CreateElement(ds.Tables[0].Columns[j].Caption.ToString().Replace(" ", "").Trim());
                            mainNode.SetAttribute("key", strMainKeyName.Replace(" ", "").Trim());
                            //for mapping in generated class property names
                            //mainNode.SetAttribute("objectmappingname", ds.Tables[0].Columns[j].Caption.ToString().Replace(" ", "").Trim());

                            mainNode.SetAttribute("operator", "EQUALS");
                            //if the value is provided from derived column
                            mainNode.SetAttribute("value", ds.Tables[0].Rows[i][j].ToString());
                            mainNode.SetAttribute("direction", strKeyDirection);
                            if (strIsDerived.Trim().ToUpper().Equals("NOTDERIVED"))
                            {
                                mainNode.SetAttribute("derived", "false");
                            }
                            else
                            {
                                mainNode.SetAttribute("derived", "true");
                            }

                            if (strIsAllowedNull.Trim().ToUpper().Equals("NULLNOTALLOWED"))
                            {
                                mainNode.SetAttribute("allownull", "false");
                            }
                            else
                            {
                                mainNode.SetAttribute("allownull", "true");
                            }

                            if (ds.Tables[0].Rows[i][j].ToString().ToUpper().Equals("TRUE")
                                || ds.Tables[0].Rows[i][j].ToString().ToUpper().Equals("FALSE")
                                || ds.Tables[0].Rows[i][j].ToString().ToUpper().Equals("YES")
                                || ds.Tables[0].Rows[i][j].ToString().ToUpper().Equals("NO"))
                            {
                                mainNode.SetAttribute("datatype", "boolean");
                            }
                            else
                            {
                                mainNode.SetAttribute("datatype", strKeyDataType);
                            }
                            // append the nodes to the parentNode without the value
                            parentNode.AppendChild(mainNode);

                            // Save to the XML file

                            if (!string.IsNullOrEmpty(objRuleSet.RulesetFileOutputDirectoryForUpdate) && !string.IsNullOrEmpty(objRuleSet.RulesetFileName))
                            {
                                xmlDoc.Save(objRuleSet.RulesetFileOutputDirectoryForUpdate + @"\" + Path.GetFileName(objRuleSet.RulesetFileName));
                            }
                        }
                    }


                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        /// <summary>
        /// Verify the XML document in contentReader against the schema in XMLClassGeneratorSchema.xsd
        /// </summary>
        /// <param name="contentReader">TextReader for XML document</param>
        private void VerifyDocumentSchema(TextReader contentReader, string inputFilePath)
        {
            // Options for XmlReader object can be set only in constructor. After the object is created, 
            // they become read-only. Because of that we need to create XmlSettings structure, 
            // fill it in with correct parameters and pass into XmlReader constructor.
            XmlReaderSettings validatorSettings = new XmlReaderSettings();
            validatorSettings.ValidationType = ValidationType.Schema;
            validatorSettings.XmlResolver = null;
            validatorSettings.ValidationEventHandler += new ValidationEventHandler(this.OnSchemaValidationError);

            //Schema is embedded in this assembly. Get its stream
            Stream schema = this.GetType().Assembly.GetManifestResourceStream("BizRuleCreator.XmlClassGeneratorSchema.xsd");

            using (XmlTextReader schemaReader = new XmlTextReader(schema))
            {
                try
                {
                    validatorSettings.Schemas.Add("http://microsoft.com/XMLClassGeneratorSchema.xsd", schemaReader);
                }
                // handle errors in the schema itself
                catch (XmlException e)
                {
                    this.GeneratorError(4, "InvalidSchemaFileEmbeddedInGenerator " + e.ToString(), 1, 1);
                    validXML = false;
                    return;
                }
                // handle errors in the schema itself
                catch (XmlSchemaException e)
                {
                    this.GeneratorError(4, "InvalidSchemaFileEmbeddedInGenerator " + e.ToString(), 1, 1);
                    validXML = false;
                    return;
                }

                using (XmlReader validator = XmlReader.Create(contentReader, validatorSettings, inputFilePath))
                {
                    validXML = true;

                    while (validator.Read())
                        ;   //empty body
                }
            }
        }

        

        /// <summary>
        /// Receives any errors that occur while validating the documents's schema.
        /// </summary>
        /// <param name="sender">Sender object</param>
        /// <param name="args">Details about the validation error that has occurred</param>
        private void OnSchemaValidationError(object sender, ValidationEventArgs args)
        {
            //signal that validation of document against schema has failed
            validXML = false;

            //Report the error (so that it is shown in the error list)
            this.GeneratorError(4, args.Exception.Message, (uint)args.Exception.LineNumber - 1, (uint)args.Exception.LinePosition - 1);

            
        }

        

        /// <summary>
        /// Method that will communicate an error via the shell callback mechanism
        /// </summary>
        /// <param name="level">Level or severity</param>
        /// <param name="message">Text displayed to the user</param>
        /// <param name="line">Line number of error</param>
        /// <param name="column">Column number of error</param>
        private void GeneratorError(uint level, string message, uint line, uint column)
        {
            IVsGeneratorProgress progress = CodeGeneratorProgress;
            if (progress != null)
            {
                progress.GeneratorError(0, level, message, line, column);
            }
        }


        
    }
}
